package net.zjitc.dao;

import net.zjitc.domain.User;
import net.zjitc.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/18.
 */
public class UserDao {
    public int pageSize = 3;
    public List<User> findPage(int page) throws SQLException {
        //连接数据库 C3P0
        Connection connection = C3P0Utils.getConnection();
        //sql
        String sql = "select * from user order by id limit ?,?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,(page-1)*pageSize);
        ps.setInt(2,pageSize);
        ResultSet result = ps.executeQuery();
        //使用集合，存放查出来的对象
        List<User> list = new ArrayList<>();
        while (result.next()){
          User user = new User();
          user.setId(result.getInt(1));
          user.setUsername(result.getString(2));
          user.setPassword(result.getString(3));
          list.add(user);
        }
        return list;
    };

    public List<User> findAll() throws SQLException {
        //连接数据库 C3P0
        Connection connection = C3P0Utils.getConnection();
        //sql
        String sql = "select * from user ";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet result = ps.executeQuery();
        //使用集合，存放查出来的对象
        List<User> list = new ArrayList<>();
        while (result.next()){
            User user = new User();
            user.setId(result.getInt(1));
            user.setUsername(result.getString(2));
            user.setPassword(result.getString(3));
            list.add(user);
        }
        return list;
    }

    public List<User> findAllByUtils() throws SQLException {
        //执行SQL的对象，参数为连接池，是DataSource类或者其子类
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user";
       //使用封装策略自动封装
        //结果集的封装策略：ResultSetHandle类，
        //因为结果有不同的各类，可能是一个集合，也可能是一个对象，
        //所以使用ResultSetHandle类的子类，封装集合的是BeanListHandler，而封闭一个对象的BeanHandler子类。
        //User.class是封装的参考模型，javaBean
        List list = (List) runner.query(sql,new BeanListHandler(User.class));
        return list;
    }

    /**
     * 按id查询 ：注意结果集的封装，BeanListHandler？？BeanHandler？？
     * @param id
     * @return
     */
    public User findById(int id ) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user where id = ?";
        User user = (User) runner.query(sql,new BeanHandler(User.class),id);
        return user;
    }

    /**
     * 插入用户方法
     * @param user
     * @return
     * @throws SQLException
     */
    public boolean insert(User user) throws SQLException {
        //
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        //
        String sql ="insert into user(id,username,password) values (?,?,?)";
       int num =  runner.update(sql,user.getId(),user.getUsername(),user.getPassword());
       if (num>0){
           return true;
       }else {
           return false;
       }
    }


}
